package com.dao.front.Impl;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.front.IMusicListFrontDao;
@Repository
public class MusicListFrontDaoImpl extends BaseDao implements IMusicListFrontDao {

	//以歌曲表查询所有
	@Override
	public List<Map<String, Object>> findAll() {
		String sql="select musicId,musicName,m.timeLength,m.musicMp3,m.musicPhoto,m.musicTypeId,m.specialId,m.musicListId,m.singerId,"
			       +" mt.musicTypeId,mt.musicTypeName,"
			       +" sp.specialId,sp.specialName,"
			       +" ml.musicListId,ml.musicListName,ml.musicListIntro,ml.musicListPhoto,"
			       +" s.singerId,s.singerName"
			       +" from music m"
			       +" left join musictype mt"
			       +" on m.musicTypeId=mt.musicTypeId"
			       +" left join special sp"
			       +" on m.specialId=sp.specialId"
			       +" left join musiclist ml"
			       +" on m.musicListId=ml.musicListId"
			       +" left join singer s"
			       +" on m.singerId=s.singerId"
			       +" group by ml.musicListId";
		return executeQuery(sql, null);
	}

	//根据歌曲id查询所有
	@Override
	public Map<String, Object> findBymid(int musicId) {
		String sql="select musicId,musicName,m.timeLength,m.musicMp3,m.musicPhoto,m.musicTypeId,m.specialId,m.musicListId,m.singerId,"
			       +" mt.musicTypeId,mt.musicTypeName,"
			       +" sp.specialId,sp.specialName,"
			       +" ml.musicListId,ml.musicListName,ml.musicListIntro,ml.musicListPhoto,"
			       +" s.singerId,s.singerName"
			       +" from music m"
			       +" left join musictype mt"
			       +" on m.musicTypeId=mt.musicTypeId"
			       +" left join special sp"
			       +" on m.specialId=sp.specialId"
			       +" left join musiclist ml"
			       +" on m.musicListId=ml.musicListId"
			       +" left join singer s"
			       +" on m.singerId=s.singerId"
			       +" where musicId=?";
		Object[] params={musicId};
		return executeQueryMap(sql, params);
	}

	//根据歌单名字查询歌单类型，歌手，专辑
	@Override
	public List<Map<String, Object>> findBymName(String musicListName) {
		String sql="select mul.musicListId,mul.musicListName,mul.musicListIntro,"
				+ " mul.musicListPhoto,mul.musicListTypeId,mu.musicId,mu.musicName,mu.musicMp3,"
				+ " mu.timeLength,mu.musicMp3,mu.musicPhoto,si.singerId,si.singerName,"
				+ " si.seName,si.state,si.address,si.`work`,si.birthday,si.singerPhoto,"
				+ " sp.specialId,sp.specialName,sp.specialType,sp.`language`,sp.issueDate,"
				+ " sp.recordCompany,sp.specialPhoto,sp.specialIntro "
				+ " from musiclist as mul left "
				+ " join music as mu on mul.musicListId=mu.musicListId "
				+ " left join singer as si on mu.singerId=si.singerId "
				+ " left join special as sp on mu.specialId=sp.specialId where musicListName = ?";
		Object[] params={musicListName};
		return executeQuery(sql, params);
	}

	

}
